<?php
/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2018/6/12
 * Time: 14:22
 */

namespace Home\Controller;


class SystemSettingController extends BaseController
{
    public function index(){
        $this->display();
    }
    public function getMenuListByPage(){
        if (!IS_AJAX) {
            $this->error("非法请求");
        }
        $id = I('post.id');
        if ($id == 0){
            $where['pid']  = array('EQ', 0);
            $count    = M('auth')->where($where)->count();
            $result = M('auth')->where($where)->order('auth_order')->select();
        }else{
            $where['pid']  = array('EQ', $id);
            $count    = M('auth')->where($where)->count();
            $result = M('auth')->where($where)->order('auth_order')->select();
        }
        $item = [];
        foreach($result as $key => $value){
            $item['id'] = $value['id'];
            if($value['pid'] == 0){
                $item['_parentId'] = null;
            }else{
                $item['_parentId'] = $value['pid'];
            }
            $item['auth_name'] = $value['auth_name'];
            $item['iconCls'] = $value['iconCls'];
            $item['state'] = $value['state'];
            if($value['auth_type']==1){
                $item['auth_type'] = '页面导航菜单';
            }else{
                $item['auth_type'] = '页面按钮';
            }
            $item['module_cn'] = $value['module_cn'];
            $item['module'] = $value['module'];
            $item['controller_cn'] = $value['controller_cn'];
            $item['controller'] = $value['controller'];
            $item['method_cn'] = $value['method_cn'];
            $item['method'] = $value['method'];
            if ($value['module'] != '' && $value['controller'] != '' && $value['method'] != '' && $value['module'] != null
                && $value['controller'] != null && $value['method'] != null) {
                $item['url'] =  $value['module'] . '/' . $value['controller'] . '/' . $value['method'];
            }else{
                $item['url'] = '';
            }
            $menuList[] = $item;
        }
        $result = array(
            "total" => $count,
            "rows"  => $menuList,
        );
        $this->ajaxReturn($result, 'JSON');
    }
    public function doEditMenu(){
        $id = I('post.id');
        $auth_name = I('post.edit_menuname');
        $module = I('post.edit_module')=='' ? null : I('post.edit_module');
        $module_cn = I('post.edit_module_cn')=='' ? null : I('post.edit_module_cn');
        $controller = I('post.edit_controller')=='' ? null : I('post.edit_controller');
        $controller_cn = I('post.edit_controller_cn')=='' ? null : I('post.edit_controller_cn');
        $method = I('post.edit_method')=='' ? null : I('post.edit_method');
        $method_cn = I('post.edit_method_cn')=='' ? null : I('post.edit_method_cn');
        $data = array(
            'auth_name' => $auth_name,
            'module' => $module,
            'module_cn' => $module_cn,
            'controller' => $controller,
            'controller_cn' => $controller_cn,
            'method' => $method,
            'method_cn' => $method_cn
        );
        $where = array('id' => $id);
        if (M('auth')->where($where)->save($data)) {
            $this->ajaxReturn(array("errorCode"=>0,"msg"=>"操作成功"),"JSON");
        }else{
            $this->ajaxReturn(array("errorCode"=>1,"msg"=>"操作失败"),"JSON");
        }
    }
    public function import()
    {
        $this->display();
    }
    /**
     * Detect upload file type
     * 检测上传文件的excel文件类型
     * @param array $file
     * @return bool $flag
     */
    private function detectUploadFileMIME($file)
    {
        //判断是03还是07的电子表格
        $flag           = false;
        $file_array     = explode(".", $file["name"]);
        $file_extension = strtolower(array_pop($file_array));
        switch ($file_extension) {
            case "xls":
                $fh  = fopen($file["tmp_name"], "rb");
                $bin = fread($fh, 8);
                fclose($fh);
                $strinfo  = @unpack("C8chars", $bin);
                $typecode = "";
                foreach ($strinfo as $num) {
                    $typecode .= dechex($num);
                }
                if ($typecode == "504b3414060") {
                    $flag = true;
                }
                break;
            case "xlsx":
                $fh  = fopen($file["tmp_name"], "rb");
                $bin = fread($fh, 4);
                fclose($fh);
                $strinfo  = @unpack("C4chars", $bin);
                $typecode = "";
                foreach ($strinfo as $num) {
                    $typecode .= dechex($num);
                }
                if ($typecode == "504b34") {
                    $flag = true;
                }
                break;
        }
        return $flag;
    }
    public function doImport()
    {
        if (!IS_AJAX) {
            $this->error("非法请求");
        }
        $type = $_FILES['xlsxfile']['type'];
        if ($this->detectUploadFileMIME($_FILES['xlsxfile'])) {
            $obj                = new \Think\Upload(); // 实例化上传类
            $obj->maxSize       = C('UPLOAD_MAX_SIZE'); //图片最大上传大小
            $obj->savePath      = ''; //设置文件上传（子）目录
            $obj->rootPath      = C('UPLOAD_PATH') . 'Import' . '/'; // 设置文件上传根目录
            $obj->saveRule      = 'uniqid'; //保存文件名
            $obj->uploadReplace = true; //覆盖同名文件
            $obj->allowExts     = array('xlsx', 'xls'); //允许上传文件的后缀名
            $obj->autoSub       = false; //不使用子目录保存
            $fileName           = date('YmdHis', time()) . '_' . rand(100, 999);
            $obj->saveName      = $fileName;
            $info               = $obj->upload();
            if (!$info) {
                $this->ajaxReturn(array('errorCode' => 1, 'msg' => $obj->getErrorMsg()), "JSON");
            } else {
                $filepath = $obj->rootPath . $info['xlsxfile']['savename'];
                $result   = $this->importExcel($filepath);
                $this->ajaxReturn($result, 'JSON');
            }
        } else {
            $this->ajaxReturn(array('errorCode' => 1, 'msg' => '数据文件格式不对'), "JSON");
        }

    }
    private function importExcel($file)
    {
        if (!file_exists($file)) {
            return array("errorCode" => 2, 'msg' => '文件没有找到');
        }
        vendor("PHPExcel.PHPExcel.IOFactory");
        //获取excel文件:获取Excel第1张表即（Sheet1）
        $objPHPExcel = \PHPExcel_IOFactory::load($file);
        $objPHPExcel->setActiveSheetIndex(0);
        $sheet1 = $objPHPExcel->getSheet(0);
        //获取行数，并把数据读取出来$data数组
        $rowCount = $sheet1->getHighestRow(); //excel行数
        $data     = array();
        try {
            for ($i = 2; $i <= $rowCount; $i++) {
                if (trim($sheet1->getCellByColumnAndRow(0, $i)->getValue()) == "") {
                    continue;
                }
                $item['noticenumber']    = $sheet1->getCellByColumnAndRow(0, $i)->getValue();
                $item['examineenumber']  = $sheet1->getCellByColumnAndRow(1, $i)->getValue();
                $item['studentname']     = $sheet1->getCellByColumnAndRow(2, $i)->getValue();
                $item['sexvalue']        = $sheet1->getCellByColumnAndRow(3, $i)->getValue();
                $item['idcard']          = $sheet1->getCellByColumnAndRow(4, $i)->getValue();
                $item['birthday']        = strtotime($sheet1->getCellByColumnAndRow(5, $i)->getValue());
                $item['nationvalue']     = $sheet1->getCellByColumnAndRow(6, $i)->getValue();
                $item['departmentvalue'] = $sheet1->getCellByColumnAndRow(7, $i)->getValue();
                $item['majorvalue']      = $sheet1->getCellByColumnAndRow(8, $i)->getValue();
                $item['finishschool']    = $sheet1->getCellByColumnAndRow(9, $i)->getValue();
                $item['telphone']        = $sheet1->getCellByColumnAndRow(10, $i)->getValue();
                $item['parenttel']       = $sheet1->getCellByColumnAndRow(11, $i)->getValue();
                $item['homeaddress']     = $sheet1->getCellByColumnAndRow(12, $i)->getValue();
                $item['postcode']        = $sheet1->getCellByColumnAndRow(13, $i)->getValue();
                $item['enrollmentyear']  = $sheet1->getCellByColumnAndRow(14, $i)->getValue();
                $data[]                  = $item;
            }
        } catch (\Exception $e) {
            return array("errorCode" => 3, 'msg' => '导入失败,数据格式有误');
        }

        $sum = count($data);
        M('student')->startTrans(); //开启事务
        $flag = true;
        foreach ($data as $k => $v) {
            try {
                M('student')->data($v)->add();
            } catch (\Exception $e) {
                $flag = false;
                break;
            }
        }
        if ($flag) {
            M('student')->commit();
            return array("errorCode" => 0, 'msg' => '导入成功,共计导入数据' . $sum . '条');
        } else {
            M('student')->rollback();
            return array("errorCode" => 3, 'msg' => '导入失败,请检查数据是否已经导入或数据格式有误');
        }
    }
    public function doExport()
    {
        if (!IS_AJAX) {
            $this->error("非法请求");
        }
        $departmentCode            = I('post.departmentCode');
        $majorCode                 = I('post.majorCode');
        $dormCode                  = I('post.dormCode');
        $studentName               = I('post.studentName');
        $where['registrationDate'] = array('exp', 'IS NOT NULL');
        if ($studentName != '') {
            $where['studentName'] = array('like', '%' . $studentName . '%');
        }
        if ($departmentCode != -1) {
            $where['departmentCode'] = $departmentCode;
        }
        if ($majorCode != -1) {
            $where['majorCode'] = $majorCode;
        }
        if ($dormCode != -1) {
            $where['dormCode'] = $dormCode;
        }
        $expCellName = array(
            array('id', '编号'),
            array('studentname', '姓名'),
            array('sexvalue', '性别'),
            array('birthday', '出生日期'),
            array('idcard', '身份证号'),
            array('nationvalue', '民族'),
            array('departmentvalue', '院系'),
            array('majorvalue', '专业'),
            array('finishschool', '毕业中学'),
            array('telphone', '联系电话'),
            array('parenttel', '家长电话'),
            array('homeaddress', '家庭住址'),
            array('postcode', '邮政编码'),
            array('dormvalue', '寝室'),
            array('enrollmentyear', '入学年份')
        );
        $expTableData = M('student')->field(array('id', 'studentname', 'sexvalue', 'DATE_FORMAT(FROM_UNIXTIME(birthday),"%Y-%m-%d")' => 'birthday', 'idcard', 'nationvalue', 'departmentvalue', 'majorvalue', 'finishschool', 'telphone', 'parenttel', 'homeaddress', 'postcode', 'dormvalue','enrollmentyear'))->where($where)->order('registrationdate desc')->select();
        $this->exportExcel($expCellName, $expTableData);
    }
    private function exportExcel($expCellName, $expTableData)
    {
        $fileName = time() . '(' . date('Y') . '年新生数据' . ')'; //导出excal 文件名称
        $cellNum  = count($expCellName); //有多少列
        $dataNum  = count($expTableData); //有多少行
        vendor("PHPExcel.PHPExcel");
        ini_set("memory_limit", "1024M");
        $objPHPExcel = new \PHPExcel(); //实例化PHPExcel类库，相当于新建一个Excel表
        $cellName    = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O',
            'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE',
            'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT',
            'AU', 'AV', 'AW', 'AX', 'AY', 'AZ');
        //在第二行插入每列的标题
        for ($i = 0; $i < $cellNum; $i++) {
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i] . '1', $expCellName[$i][1]);
        }
        //从第三行开始插入数据
        for ($i = 0; $i < $dataNum; $i++) {
            for ($j = 0; $j < $cellNum; $j++) {
                $objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j] . ($i + 2), $expTableData[$i][$expCellName[$j][0]]);
            }
        }
        $objSheet = $objPHPExcel->getActiveSheet(); //获取当前活动sheet
        $objSheet->setTitle('sheet1'); //给当前的活动sheet起个名称
        header('pragma:public');
        header('Content-type:application/vnd.ms-excel;charset=utf-8;name="' . $fileName . '.xlsx"');
        header("Content-Disposition:attachment;filename=$fileName.xlsx"); //attachment新窗口打印inline本窗口打印
        header('Cache-Control: max-age=0');
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
        $response  = array(
            'success' => true,
            'url'     => $this->saveExcelToLocalFile($objWriter, $fileName),
        );
        if ($response) {
            $this->ajaxReturn($response, "json");
        }
    }
    private function saveExcelToLocalFile($objWriter, $filename)
    {
        $filePath = './uploads/Export/' . $filename . '.xlsx';
        $objWriter->save($filePath);
        return $filePath;
    }
}